-- 项目客户客诉率指标值表
CREATE EXTERNAL TABLE IF NOT EXISTS jms_dm.dm_project_customer_complaint_indicator_dt(
input_date                      String           COMMENT '查询日期(运单的录入日期)',
project_customer_id             String           COMMENT '项目客户id',
project_customer_name           String           COMMENT '项目客户名称',
subordinate_agent_code          String           COMMENT '代理区编码',
subordinate_agent_name          String           COMMENT '代理区名称',
franchisee_code                 String           COMMENT '加盟商编码',
franchisee_name                 String           COMMENT '加盟商名称',
network_code                    String           COMMENT '网点编码',
network_name                    String           COMMENT '网点名称',
indicator_value                 Double           COMMENT '指标值',
indicator_code                  String           COMMENT '指标编码',
indicator_name                  String           COMMENT '指标名称',
alert_value                     Double           COMMENT '预警阈值',
is_alert                        smallint         COMMENT '是否预警：0不告警，1告警'
)
COMMENT '项目客户客诉率指标值表'
PARTITIONED BY (dt string COMMENT '日期(yyyy-MM-dd)',report_name string COMMENT '报表英文名称')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_project_customer_complaint_indicator_dt'
TBLPROPERTIES (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);

-- 项目客户客诉率指标值表(90天全量表),用于同步给doris
CREATE EXTERNAL TABLE IF NOT EXISTS jms_dm.dm_project_customer_complaint_indicator_h(
input_date                      String           COMMENT '查询日期(运单的录入日期)',
project_customer_id             String           COMMENT '项目客户id',
project_customer_name           String           COMMENT '项目客户名称',
subordinate_agent_code          String           COMMENT '代理区编码',
subordinate_agent_name          String           COMMENT '代理区名称',
franchisee_code                 String           COMMENT '加盟商编码',
franchisee_name                 String           COMMENT '加盟商名称',
network_code                    String           COMMENT '网点编码',
network_name                    String           COMMENT '网点名称',
indicator_value                 Double           COMMENT '指标值',
indicator_code                  String           COMMENT '指标编码',
indicator_name                  String           COMMENT '指标名称',
alert_value                     Double           COMMENT '预警阈值',
is_alert                        smallint         COMMENT '是否预警：0不告警，1告警'
)
COMMENT '项目客户指标值表(90天全量表)'
PARTITIONED BY (dt string COMMENT '日期(yyyy-MM-dd)',report_name string COMMENT '报表英文名称')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_project_customer_complaint_indicator_h'
TBLPROPERTIES (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);

CREATE TABLE `dwd_project_customer_complaint_indicator` (
  `input_date` date NOT NULL COMMENT "查询日期(运单的录入日期)",
  `project_customer_id` varchar(50) NULL COMMENT "项目客户id",
  `network_code` varchar(50) NULL COMMENT "网点编码",
  `project_customer_name` varchar(50) NULL COMMENT "项目客户名称",
  `subordinate_agent_code` varchar(50) NULL COMMENT "代理区编码",
  `subordinate_agent_name` varchar(50) NULL COMMENT "代理区名称",
  `franchisee_code` varchar(50) NULL COMMENT "加盟商编码",
  `franchisee_name` varchar(50) NULL COMMENT "加盟商名称",
  `network_name` varchar(50) NULL COMMENT "网点名称",
  `indicator_value` double NULL COMMENT "指标值",
  `indicator_code` varchar(50) NULL COMMENT "指标编码",
  `indicator_name` varchar(50) NULL COMMENT "指标名称",
  `alert_value` double NULL COMMENT "预警阈值",
  `is_alert` smallint(6) NULL COMMENT "是否预警：0不告警，1告警"
) ENGINE=OLAP
DUPLICATE KEY(`input_date`, `project_customer_id`, `network_code`)
COMMENT "项目客户客诉指标值表"
DISTRIBUTED BY HASH(`project_customer_id`) BUCKETS 3
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false",
"storage_format" = "V2"
);